package cn.sheep.violet.report

import cn.sheep.violet.config.ConfigHandler
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.{SQLContext, SaveMode}

/**
  * 地域分布统计 -sql
  * author: old sheep
  * QQ: 64341393 
  * Created 2018/10/16
  */
object AreaAnalysisSQL {

    def main(args: Array[String]): Unit = {

        val sparkConf = new SparkConf()
        sparkConf.setAppName("地域分布统计-core")
        sparkConf.setMaster("local[*]")
        // 设置spark程序采用的序列化方式
        sparkConf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
        val sc = new SparkContext(sparkConf)
        val sQLContext = new SQLContext(sc)

        // 读取数据
        val dataFrame = sQLContext.read.parquet(ConfigHandler.parquetFilePath)

        //sql -> 表
        dataFrame.registerTempTable("logs")

        /*sQLContext.sql(
            """
              |
              |select provincename, cityname,
              |sum(case when requestmode=1 and processnode>=1 then 1 else 0 end) adRawReq,
              |sum(case when requestmode=1 and processnode>=2 then 1 else 0 end) adEffReq,
              |sum(case when requestmode=1 and processnode=3 then 1 else 0 end) adReq,
              |sum(case when iseffective=1 and isbilling=1 and isbid=1 and adorderid!=0 then 1 else 0 end) adRtbReq,
              |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) adSuccReq,
              |sum(case when requestmode=2 and iseffective=1 then 1 else 0 end) adShow,
              |sum(case when requestmode=3 and iseffective=1 then 1 else 0 end) adClick,
              |sum(case when iseffective=1 and isbilling=1 and iswin=1 then winprice/1000 else 0 end) adCost,
              |sum(case when iseffective=1 and isbilling=1 and iswin=1 then adpayment/1000 else 0 end) adConsumption
              |
              |from logs
              |group by provincename, cityname
            """.stripMargin).show()*/


        sQLContext.sql(
            """
              |select provincename, cityname,
              |sum(if(requestmode=1 and processnode>=1, 1, 0)) adRawReq,
              |sum(if(requestmode=1 and processnode>=2, 1, 0)) adEffReq,
              |sum(case when requestmode=1 and processnode=3 then 1 else 0 end) adReq,
              |sum(case when iseffective=1 and isbilling=1 and isbid=1 and adorderid!=0 then 1 else 0 end) adRtbReq,
              |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) adSuccReq,
              |sum(case when requestmode=2 and iseffective=1 then 1 else 0 end) adShow,
              |sum(case when requestmode=3 and iseffective=1 then 1 else 0 end) adClick,
              |sum(case when iseffective=1 and isbilling=1 and iswin=1 then winprice/1000 else 0 end) adCost,
              |sum(case when iseffective=1 and isbilling=1 and iswin=1 then adpayment/1000 else 0 end) adConsumption
              |
              |from logs
              |group by provincename, cityname
            """.stripMargin)
            .write.mode(SaveMode.Overwrite).jdbc(ConfigHandler.url, "orc_report_area1", ConfigHandler.props)


        sc.stop()

    }

}
